﻿CREATE TABLE [Products].[datProducts] (
    [ProductID]          INT             IDENTITY (1, 1) NOT NULL,
    [SubCategorieID]     NCHAR (10)      NOT NULL,
    [CategorieID]        NCHAR (10)      NULL,
    [ProductTypeGroupID] NCHAR (10)      CONSTRAINT [DF_datProducts_ProductType] DEFAULT (N'Pd') NOT NULL,
    [ProductName]        NVARCHAR (150)  NOT NULL,
    [Unity]              NVARCHAR (50)   NOT NULL,
    [ItemNumber]         NVARCHAR (50)   NULL,
    [ListPrice]          DECIMAL (11, 2) CONSTRAINT [DF_datProducts_ListPrice] DEFAULT ((0)) NOT NULL,
    [Rabatt1]            DECIMAL (11, 2) CONSTRAINT [DF_datProducts_Rabatt1] DEFAULT ((0)) NOT NULL,
    [Rabatt2]            DECIMAL (11, 2) CONSTRAINT [DF_datProducts_Rabatt2] DEFAULT ((0)) NOT NULL,
    [Skonto]             DECIMAL (11, 2) CONSTRAINT [DF_datProducts_Skonto] DEFAULT ((0)) NOT NULL,
    [CostPrice]          AS              (case when [ListPrice]<>(0) then round(((([ListPrice]*((100)-[Rabatt1]))*((100)-[Rabatt2]))*((100)-[Skonto]))/(1000000),(2)) else (0) end) PERSISTED,
    [SurCharge]          DECIMAL (11, 2) CONSTRAINT [DF_datProducts_SurCharge] DEFAULT ((0)) NOT NULL,
    [SalesPrice]         AS              (case when [ListPrice]<>(0) then case when [SurCharge]<>(0) then CONVERT([decimal](11,2),ceiling((((([ListPrice]*((100)-[Rabatt1]))*((100)-[Rabatt2]))*((100)-[Skonto]))/(1000000))/((1)-[SurCharge]/(100))),(0)) else [ListPrice] end else (0) end) PERSISTED,
    [ContributionMargin] AS              (case when [ListPrice]<>(0) then case when [SurCharge]<>(0) then [SurCharge] else round(((case when [SurCharge]<>(0) then CONVERT([decimal](11,2),ceiling((((([ListPrice]*((100)-[Rabatt1]))*((100)-[Rabatt2]))*((100)-[Skonto]))/(1000000))/((1)-[SurCharge]/(100))),(0)) else [ListPrice] end-round(((([ListPrice]*((100)-[Rabatt1]))*((100)-[Rabatt2]))*((100)-[Skonto]))/(1000000),(2)))/case when [SurCharge]<>(0) then CONVERT([decimal](11,2),ceiling((((([ListPrice]*((100)-[Rabatt1]))*((100)-[Rabatt2]))*((100)-[Skonto]))/(1000000))/((1)-[SurCharge]/(100))),(0)) else [ListPrice] end)*(100),(0)) end else (0) end),
    [TaxCode]            TINYINT         NULL,
    [Effort]             DECIMAL (11, 2) CONSTRAINT [DF_datProducts_Effort] DEFAULT ((100)) NOT NULL,
    [Hour]               DECIMAL (11, 3) CONSTRAINT [DF_datProducts_Hour] DEFAULT ((0)) NOT NULL,
    [ApplyFrom]          DATETIME2 (7)   CONSTRAINT [DF_datProducts_ApplyFrom] DEFAULT (sysdatetime()) NOT NULL,
    [ApplyTill]          DATETIME2 (7)   CONSTRAINT [DF_datProducts_ApplyTill] DEFAULT ('39991231') NOT NULL,
    [ProductState]       TINYINT         CONSTRAINT [DF_datProducts_ProductState] DEFAULT ((0)) NOT NULL,
    [Stock]              BIT             CONSTRAINT [DF_datProducts_Stock] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_datProducts] PRIMARY KEY CLUSTERED ([ProductID] ASC)
);






GO
CREATE NONCLUSTERED INDEX [IX_Products_datProducts_ListPrice]
    ON [Products].[datProducts]([ListPrice] ASC);

